-- Drop not numeric data
update TravelPackage   
set VacationLength = CASE WHEN ISNUMERIC(VacationLength + '.e0') = 1 THEN 
						CASE WHEN CONVERT(float, VacationLength) BETWEEN -2147483648 AND 2147483647 
						THEN CONVERT(int, VacationLength) 
						END 
					ELSE null END
go

alter table TravelPackage
alter column VacationLength int
go
ALTER PROCEDURE [dbo].[spContract_SetStatus]
(
	@ContractId int,
	@Status varchar(50),
	
	@ContractLinkValidDays int = null,
	@IP varchar(20) = null,
	@Pin varchar(4) out
)
AS
BEGIN
	set nocount on
	set xact_abort on
	
	if (@Status = 'Sent')
	begin
		declare @LinkCode varchar(255), @ExpiredLinkDate datetime
		set @LinkCode = CONVERT(varchar(255), NEWID())
		set @ExpiredLinkDate = DATEADD(day, @ContractLinkValidDays, convert(date, GETDATE()))
		
		-- if first code generation
		if exists(select 1 from [Contract] where (ContractId = @ContractId) and (LinkCode is null))
			update [Contract]
			set [Status] = @Status, ContractDate = CONVERT(date, getdate()),
				LinkCode = @LinkCode, ExpiredLinkDate = @ExpiredLinkDate
			where ContractId = @ContractId
		else
			update [Contract]
			set LinkCode = @LinkCode, ExpiredLinkDate = @ExpiredLinkDate
			where ContractId = @ContractId
	end
	
	if (@Status = 'Viewed')
	begin
		if exists(select 1 from [Contract] where (ContractId = @ContractId) and (ViewDate is null))
			update [Contract]
			set [Status] = @Status,
				ViewDate = GETDATE(), ViewIP = @IP
			where ContractId = @ContractId
	end
	
	if (@Status = 'Signed')
	begin
		if exists(select 1 from [Contract] where (ContractId = @ContractId) and (SignedDate is null))
		begin
			declare @PackageId int, @ContactId int
			
			select @PackageId = PackageId, @ContactId = ContactId
			from [Contract] (nolock)
			where ContractId = @ContractId
			
			if exists(select 1 from Contact (nolock) where (ContactId = @ContactId) and (Pin is null))
				exec spPin_Create @Pin = @Pin out
						
			begin transaction
				update [Contract]
				set [Status] = @Status,
					SignedDate = GETDATE(), SignedIP = @IP
				where ContractId = @ContractId
				
				-- if pin was generated, than write it
				if @Pin is not null
					update Contact
					set Pin = @Pin
					where (ContactId = @ContactId)
				
				if (@PackageId is not null)
				begin
					insert into ClientPackage
					(ClientId, [Status], StartDate, ExpDate, PackageId)
					values(@ContactId, 'AVAILABLE', GETDATE(), GETDATE(), @PackageId)
				end
			commit transaction
		end
	end
END
go
ALTER PROCEDURE [dbo].[spAPI_ClientPackageChoice_Set]
(
	@TravelId int,
	@ClientId int,
	@PkgDestId int,
	@ChoiceId varchar(max),
	@StartDate datetime = null
)
AS
BEGIN
	set nocount on
	set xact_abort on
	
	declare @IdErr int
	set @IdErr = 0
		
	if not exists(select 1 from ClientPackage (nolock) where TravelId = @TravelId)
	begin
		set @IdErr = 2
		select @IdErr as IdErr
		return
	end
	
	if not exists(select 1 from ClientPackage (nolock) where TravelId = @TravelId and ClientId = @ClientId)
	begin
		set @IdErr = 3
		select @IdErr as IdErr
		return
	end
	
	begin transaction
		update ClientPackage
		set PkgDestId = @PkgDestId, [Status] = 'PENDING', StartDate = ISNULL(@StartDate, StartDate)
		where TravelId = @TravelId
			and ClientId = @ClientId
		
		delete 
		from ClientPackageChoice 
		where TravelId = @TravelId
		
		insert into ClientPackageChoice
		(TravelId, ChoiceId)
		select @TravelId, vw.Item
		from dbo.fnStringSplit(@ChoiceId, ',') as vw
	commit transaction
		
	select @IdErr as IdErr
END
go